Option Strict On
Option Explicit On

Imports System
Imports System.data
Imports System.Data.SqlClient
Imports KadGen.MetadataWrapper
Imports System.Collections.Generic

''' <summary>
''' Loads data into the standard metadata wrapper from a SQL server database
''' </summary>
''' <remarks>Consider this implementation interim until the Oracle implementation is complete</remarks>
Public Class LoadSqlServer
   Inherits LoadMetadataBase

   Private Const SpTypeRetrieve As String = "Retrieve"
   Private Const SpTypeSetSelect As String = "SetSelect"

   Private mExcludedSystemTableNames() As String = New String() {"dtproperties", "sysconstraints", "syssegments", "sysdiagrams"}

   Private mDbsStructures As New KadGen.MetadataWrapper.DbsDataStructureCollection
   Private mDbsStructure As DbsDataStructure

   Private mTableTable As DataTable
   Private mColumnTable As DataTable
   Private mPrimaryKeyTable As DataTable
   Private mUniqueTable As DataTable
   Private mUniqueIndexTable As DataTable
   Private mUniqueIndexColumnTable As DataTable
   Private mViewTable As DataTable
   Private mViewColumnTable As DataTable
   Private mReferentialTable As DataTable
   Private mIndexTable As DataTable
   Private mIndexColumnsTable As DataTable
   Private mProcedureTable As DataTable
   Private mProcedureParametersTable As DataTable
   Private mTablePrivilegesTable As DataTable
   Private mColumnPrivilegesTable As DataTable
   Private mColumnCheckConstraintTable As DataTable
   Private mTableCheckConstraintTable As DataTable
   Private mKeyColumnTable As DataTable
   Private mUserDefinedTypeTable As DataTable
   Private mUdtCheckConstraintTable As DataTable
   Private mConnection As SqlConnection
   Private mExtendedPropertyProvider As ExtendedPropertyProvider

   ''' <summary>
   ''' Constructor for LoadSqlServer
   ''' </summary>
   ''' <param name="serverName">Name of the server to use</param>
   ''' <param name="databaseName">Name of the database to use</param>
   Public Sub New( _
               ByVal serverName As String, _
               ByVal databaseName As String)
      MyBase.New(serverName, databaseName)
   End Sub

   Protected Overrides Function InternalLoad() As MetadataWrapper.DbsMetaDataRoot
      Dim wrapper As New KadGen.MetadataWrapper.DbsMetaDataRoot(mDbsStructures, False)
      mDbsStructure = New DbsDataStructure(mDbsStructures, DatabaseName)
      mDbsStructures.Add(mDbsStructure)
      Using connection As New System.Data.SqlClient.SqlConnection(GetConnectionString)
         connection.Open()
         mConnection = connection 'Share it
         mExtendedPropertyProvider = New ExtendedPropertyProvider(connection, _
                  SkipExtendedProperties, CustomAttributeNamespace, CustomAttributePrefix)
         mExtendedPropertyProvider.AddExtendedDatabaseProperties(mDbsStructure)
         mTableTable = connection.GetSchema("Tables")
         mColumnTable = connection.GetSchema("Columns")
         mPrimaryKeyTable = GetPrimaryKeyTable()
         mUniqueTable = GetUniqueTable()
         mUniqueIndexTable = GetUniqueIndexTable()
         mUniqueIndexColumnTable = GetUniqueIndexColumnTable()
         mKeyColumnTable = GetKeyColumnTable()
         mTablePrivilegesTable = GetTablePrivilegesTable()
         mColumnPrivilegesTable = GetColumnPrivilegesTable()
         mColumnCheckConstraintTable = GetColumnCheckConstraintsTable()
         mTableCheckConstraintTable = GetTableCheckConstraintsTable()
         mViewTable = connection.GetSchema("Views")
         mViewColumnTable = connection.GetSchema("ViewColumns")
         mReferentialTable = GetReferentialTable()
         mIndexTable = connection.GetSchema("Indexes")
         mIndexColumnsTable = connection.GetSchema("IndexColumns")
         mProcedureTable = connection.GetSchema("Procedures")
         mProcedureParametersTable = connection.GetSchema("ProcedureParameters")
         mUserDefinedTypeTable = GetUdtTable()
         mUdtCheckConstraintTable = GetUdtCheckConstraintsTable()
         connection.Close()
         AppendTableInsertPriority()
         FillTablesAndViews()
         If Not SkipStoredProcs Then
            FillStoredProcsAndFunctions()
         End If
         FillUserDefiendTypes()
         FillHierarchy()
      End Using
      Return wrapper
   End Function

   Private Function GetConnectionString() As String
      Dim connectionBuilder As New SqlConnectionStringBuilder()
      connectionBuilder.DataSource = ServerName
      connectionBuilder.InitialCatalog = DatabaseName
      connectionBuilder.IntegratedSecurity = True
      Return connectionBuilder.ConnectionString
   End Function

   Private Sub FillTablesAndViews()
      For Each tableRow As DataRow In mTableTable.Rows
         If Not ShouldExcludeTable(tableRow("TABLE_NAME").ToString()) Then
            FillTable(tableRow)
         End If
      Next
   End Sub

   Private Function ShouldExcludeTable(ByVal tableName As String) As Boolean
      For Each name As String In Me.mExcludedSystemTableNames
         If String.Compare(tableName, name, True) = 0 Then
            Return True = True
         End If
      Next
      If tableName.StartsWith("msdynamic", StringComparison.InvariantCultureIgnoreCase) Or _
               tableName.StartsWith("msmerge_", StringComparison.InvariantCultureIgnoreCase) Or _
               tableName.StartsWith("msrepl_", StringComparison.InvariantCultureIgnoreCase) Or _
               tableName.StartsWith("sysrepl", StringComparison.InvariantCultureIgnoreCase) Or _
               tableName.StartsWith("sysmerge", StringComparison.InvariantCultureIgnoreCase) Then
         Return True
      End If
      Return False
   End Function

   Private Sub FillStoredProcsAndFunctions()
      For Each row As DataRow In mProcedureTable.Rows
         FillStoredProc(row)
      Next
   End Sub

   Private Sub FillUserDefiendTypes()
      For Each row As DataRow In mUserDefinedTypeTable.Rows
         FillUserDefinedType(row)
      Next
   End Sub

   Private Sub FillHierarchy()
      Dim stack As New Stack(Of String)
      Me.mDbsStructure.DbsHierarchy = New DbsHierarchy(Me.mDbsStructures)
      For Each table As DbsTable In mDbsStructure.DbsTables
         ' The last is a self-join which we want to include
         If table.DbsParentTables Is Nothing OrElse table.DbsParentTables.Count = 0 OrElse _
               (table.DbsParentTables.Count = 1 AndAlso table.DbsParentTables(0).Name = table.Name) Then
            ' It's a root
            Me.mDbsStructure.DbsHierarchy.DbsHTables.Add(GetHierarchyNode(stack, table))
         End If
      Next
   End Sub

   Private Sub FillTable( _
            ByVal tableRow As DataRow)
      Dim columnRows As DataRow() = mColumnTable.Select(TableMatch(tableRow), "ordinal_position")
      Dim originalName As String = tableRow("table_name").ToString()
      Dim tableName As String = Utility.GetTableName(originalName)
      Dim name As String = Utility.FixName(tableName, RemoveAllPrefixes, Prefixes, FriendlyCaps, RemoveUnderscores, KeywordTreatment, TargetLanguage, BlankTreatment)
      Dim prefix As String = Utility.GetPrefix(originalName, Prefixes, RemoveAllPrefixes)
      Dim singularName As String = Utility.GetSingular(name)
      Dim pluralName As String = Utility.GetPlural(name)
      Dim displayName As String = Utility.GetDisplayName(columnRows, singularName)
      Dim caption As String = Utility.MakeCaption(singularName)
      Dim pluralCaption As String = Utility.MakeCaption(pluralName)
      Dim xmlFriendlyName As String = Utility.GetXmlFriendlyName(originalName)
      Dim isLookup As Boolean = LookupPrefix.Length > 0 AndAlso originalName.StartsWith(LookupPrefix)
      Dim schemaName As String = tableRow("table_schema").ToString

      Dim wrapper As WrapperBase
      Dim columnInfoTable As DataTable = GetColumnInfoTable(tableName)

      If tableRow("table_type").ToString() = "VIEW" Then
         Dim dbsView As New DbsView(mDbsStructures, name, originalName, prefix, _
                        singularName, pluralName, displayName, caption, _
                        pluralCaption, xmlFriendlyName, isLookup, schemaName)
         Me.mDbsStructure.DbsViews.Add(dbsView)
         wrapper = dbsView
         AddViewPrivileges(tableRow, dbsView)
      Else
         Dim deletePriority As Int32 = 0 ' Obsolete, kept for backwards compatibility
         Dim insertPriority As Int32 = CInt(tableRow("LOAD_PRIORITY"))

         Dim dbsTable As New DbsTable(mDbsStructures, name, originalName, prefix, _
                        singularName, pluralName, displayName, caption, _
                        pluralCaption, xmlFriendlyName, isLookup, _
                        deletePriority, insertPriority, schemaName)
         AddPrimaryKeys(tableRow, dbsTable)
         AddUniqueKeys(tableRow, dbsTable)
         AddParentTables(tableRow, dbsTable)
         AddChildTables(tableRow, dbsTable)
         Me.mDbsStructure.DbsTables.Add(dbsTable)
         wrapper = dbsTable
         AddTablePrivileges(tableRow, dbsTable)
      End If
      mExtendedPropertyProvider.AddExtendedTableProperties(wrapper, schemaName, originalName)
      For Each columnRow As DataRow In columnRows
         FillTableColumn(columnRow, schemaName, originalName, columnInfoTable, wrapper)
      Next
   End Sub

   Private Sub FillTableColumn( _
            ByVal columnRow As Data.DataRow, _
            ByVal schema As String, _
            ByVal tableName As String, _
            ByVal columnInfoTable As DataTable, _
            ByVal dbsTableOrView As WrapperBase)
      Dim primaryKeys As DataRow() = GetConstraintRows(Me.mPrimaryKeyTable, tableName, schema, dbsTableOrView)
      Dim uniqueKeys As DataRow() = GetConstraintRows(Me.mUniqueTable, tableName, schema, dbsTableOrView)
      Dim originalName As String = columnRow("column_name").ToString()
      Dim name As String = originalName
      If ColumnRemoveTablePrefix Then
         If name.StartsWith(tableName, StringComparison.InvariantCultureIgnoreCase) Then
            name = KadGen.Utility.Tools.SubstringAfter(name, tableName)
         End If
      End If
      name = Utility.FixName(name, ColumnRemoveAllPrefixes, ColumnPrefixes, _
                 ColumnFriendlyCaps, ColumnRemoveUnderscores, ColumnKeywordTreatment, TargetLanguage, _
                 ColumnBlankTreatment)
      Dim sqlType As String = columnRow("data_type").ToString
        Dim netType As String = Utility.GetNetTypeFromSqlType(sqlType)
        Dim caption As String = Utility.MakeCaption(name)
        Dim allowNulls As Boolean = columnRow("is_nullable").ToString.ToLower = "yes"
        Dim isAutoIncrement As Boolean = Me.IsAutoIncrement(columnInfoTable, originalName, dbsTableOrView)
        Dim defaultValue As String = columnRow("column_default").ToString()
        Dim maxLength As Int32
        If String.Compare(sqlType, "text", True) <> 0 AndAlso _
                 String.Compare(sqlType, "ntext", True) <> 0 AndAlso _
                 String.Compare(sqlType, "timestamp", True) <> 0 Then
            maxLength = Utility.GetInteger(columnRow("character_maximum_length"))
        End If
        Dim isPrimaryKey As Boolean = IsInConstraint(primaryKeys, originalName)
        Dim precision As Int32 = Utility.GetInteger(columnRow("numeric_precision"))
        Dim scale As Int32 = Utility.GetInteger(columnRow("numeric_scale"))
        Dim unique As Boolean = IsInConstraint(uniqueKeys, originalName)
        Dim ordinal As Int32 = Utility.GetInteger(columnRow("ordinal_position"))
        Dim useForDesc As Boolean
        Dim wrapper As WrapperBase
        Dim addColumnCheckConstraintDelegate As AddColumnCheckConstraintDelegate
        ' TODO: Figure out why UDT information (DOMAIN) is not in wrapper

        Dim dbsTable As DbsTable = TryCast(dbsTableOrView, DbsTable)
        If dbsTable IsNot Nothing Then
            Dim dbsTableColumn As New DbsTableColumn(mDbsStructures, name, originalName, _
                     caption, ordinal, allowNulls, sqlType, netType, maxLength, _
                     isPrimaryKey, precision, scale, isAutoIncrement, useForDesc, _
                     defaultValue)
            addColumnCheckConstraintDelegate = AddressOf dbsTableColumn.DbsCheckConstraints.Add
            AddTableColumnPrivileges(schema, tableName, columnRow, dbsTableColumn)
            wrapper = dbsTableColumn
            dbsTable.DbsTableColumns.Add(dbsTableColumn)
        Else
            Dim dbsView As DbsView = TryCast(dbsTableOrView, DbsView)
            If dbsView IsNot Nothing Then
                Dim dbsViewColumn As New DbsViewColumn(mDbsStructures, name, originalName, _
                         caption, ordinal, allowNulls, sqlType, netType, maxLength, _
                         isPrimaryKey, precision, scale, isAutoIncrement, useForDesc, _
                         defaultValue)
                addColumnCheckConstraintDelegate = AddressOf dbsViewColumn.DbsCheckConstraints.Add
                AddViewColumnPrivileges(schema, tableName, columnRow, dbsViewColumn)
                wrapper = dbsViewColumn
                dbsView.DbsViewColumns.Add(dbsViewColumn)
            End If
        End If
        mExtendedPropertyProvider.AddExtendedTableColumnProperties(wrapper, schema, _
                    tableName, originalName)
        AddColumnCheckConstraints(originalName, schema, tableName, columnRow, addColumnCheckConstraintDelegate)
    End Sub

   Private Sub FillStoredProc(ByVal procedureRow As DataRow)
      Dim spName As String = procedureRow("ROUTINE_NAME").ToString
      If Not spName.StartsWith("dt_", StringComparison.InvariantCultureIgnoreCase) And _
         Not spName.StartsWith("msmerge_", StringComparison.InvariantCultureIgnoreCase) Then
         Dim schema As String = procedureRow("ROUTINE_SCHEMA").ToString
         Dim modified As String = procedureRow("LAST_ALTERED").ToString
         Dim created As String = procedureRow("CREATED").ToString
         Dim type As String = Nothing
         Dim routineType As String = procedureRow("ROUTINE_TYPE").ToString
         Dim wrapper As WrapperBase
         Dim addStoredProcParameterDelegate As AddStoredProcParameterDelegate
         Dim params As DbsStoredProcParameterCollection
         If Utility.IsRetrieveRecordset(spName, GetSelectPatterns) Then
            type = SpTypeRetrieve
         End If
         If Utility.IsSetSelectRecordset(spName, GetSetSelectPatterns) Then
            type = SpTypeSetSelect
         End If
         If routineType = "PROCEDURE" Then
            Dim dbsStoredProc As New DbsStoredProc(mDbsStructures, spName, created, modified, type)
            wrapper = dbsStoredProc
            addStoredProcParameterDelegate = AddressOf dbsStoredProc.DbsStoredProcParameters.Add
            params = dbsStoredProc.DbsStoredProcParameters
            Me.mDbsStructure.DbsStoredProcs.Add(dbsStoredProc)
            mExtendedPropertyProvider.AddExtendedStoredProcProperties(wrapper, schema, spName)
            FillParameters(procedureRow, addStoredProcParameterDelegate)
            dbsStoredProc.DbsDataSet = FillRecordsets(procedureRow, spName, params, type, dbsStoredProc)
         Else
            Dim dbsFunction As New DbsFunction(mDbsStructures, spName, created, modified)
            wrapper = dbsFunction
            addStoredProcParameterDelegate = AddressOf dbsFunction.DbsStoredProcParameters.Add
            params = dbsFunction.DbsStoredProcParameters
            Me.mDbsStructure.DbsFunctions.Add(dbsFunction)
            mExtendedPropertyProvider.AddExtendedStoredProcProperties(wrapper, schema, spName)
            dbsFunction.DbsDataSet = FillRecordsets(procedureRow, spName, params, type, dbsFunction)
         End If
         ' TODO: See if we want a mechanism to include stored proc source
         ' TODO: Add stored proc privileges which dont' seem to be availabe from information schema
      End If
   End Sub

   Private Sub FillParameters( _
            ByVal procedureRow As DataRow, _
            ByVal addStoredProcParameterDelegate As AddStoredProcParameterDelegate)
      Dim parameterRows As DataRow() = mProcedureParametersTable.Select(ProcedureMatch(procedureRow), "ORDINAL_POSITION")
      For Each parameterRow As DataRow In parameterRows
         FillParameter(parameterRow, addStoredProcParameterDelegate)
      Next
   End Sub

   Private Delegate Sub AddStoredProcParameterDelegate(ByVal dbsParameter As DbsStoredProcParameter)
   Private Sub FillParameter( _
            ByVal parameterRow As DataRow, _
            ByVal addStoredProcParameter As AddStoredProcParameterDelegate)
      Dim name As String = parameterRow("PARAMETER_NAME").ToString
      Dim ordinal As Int32 = CInt(parameterRow("ORDINAL_POSITION"))
      Dim direction As String = parameterRow("PARAMETER_MODE").ToString
      Dim type As String = parameterRow("DATA_TYPE").ToString
      Dim maxlength As Int32
      If parameterRow("CHARACTER_MAXIMUM_LENGTH") IsNot DBNull.Value Then
         maxlength = CInt(parameterRow("CHARACTER_MAXIMUM_LENGTH"))
      End If
      Dim dbsParameter As New DbsStoredProcParameter(mDbsStructures, name, ordinal, type, maxlength, direction)
      addStoredProcParameter(dbsParameter)
   End Sub

   Private Function FillRecordsets( _
            ByVal procedureRow As DataRow, _
            ByVal spName As String, _
            ByVal spParameters As DbsStoredProcParameterCollection, _
            ByVal type As String, _
            ByVal spWrapper As WrapperBase) _
            As DbsDataSet
      Dim ds As DataSet = Nothing
      Dim dbsDataSet As DbsDataSet = Nothing
      If type = SpTypeRetrieve Or type = SpTypeSetSelect Then
         Try
            dbsDataSet = New DbsDataSet(Me.mDbsStructures)
            ds = RunStoredProc(spName, spParameters)
            For i As Int32 = 0 To ds.Tables.Count - 1
               Dim dt As DataTable = ds.Tables(i)
               Dim dbsRecordset As New DbsRecordset(mDbsStructures, dt.TableName)
               For Each col As Data.DataColumn In dt.Columns
                  Dim name As String = col.ColumnName
                  Dim netType As String = col.DataType.ToString
                        Dim caption As String = Utility.MakeCaption(name)
                  Dim allowDBNull As Boolean = col.AllowDBNull
                  Dim autoIncrement As Boolean = col.AutoIncrement
                  Dim defaultValue As String = col.DefaultValue.ToString
                  Dim maxLength As Int32 = col.MaxLength
                  Dim unique As Boolean = col.Unique
                  Dim ordinal As Int32 = col.Ordinal
                  dbsRecordset.DbsColumns.Add(New DbsColumn(mDbsStructures, name, netType, caption, _
                           allowDBNull, autoIncrement, defaultValue, maxLength, unique, ordinal))
               Next
               dbsDataSet.DbsRecordsets.Add(dbsRecordset)
            Next
         Catch ex As System.Exception
            Utility.LogForUser("Stored proc failed", LogId.StoredProcedureCallFailed, spWrapper)
         Finally
            If ds IsNot Nothing Then
               ds.Dispose()
            End If
         End Try
      End If
      Return dbsDataSet
   End Function

   Private Sub FillUserDefinedType(ByVal udtRow As DataRow)
      Dim domain As String = udtRow("DOMAIN_NAME").ToString
      Dim defaultValue As String = udtRow("DOMAIN_DEFAULT").ToString
      Dim type As String = udtRow("DATA_TYPE").ToString
      Dim max = udtRow("CHARACTER_MAXIMUM_LENGTH")
      Dim maxLength = 0
      If max IsNot DBNull.Value Then
         maxLength = CInt(max)
      End If
      ' TODO: WHy doesn't the udt store the domain or some other identifier
      Dim dbsUdt As New DbsUserDefinedType(mDbsStructures, defaultValue, type, maxLength)
      AddUdtCheckConstraints(udtRow, dbsUdt)

      Me.mDbsStructure.DbsUserDefinedTypes.Add(dbsUdt)
   End Sub

#Region "Private helper methods"

   Private Function GetHierarchyNode( _
            ByVal stack As Stack(Of String), _
            ByVal table As DbsTable) _
            As DbsHTable
      Dim htable As New DbsHTable(Me.mDbsStructures, table.Name)
      If stack.Contains(table.Name) Then
         ' recursion, so bail
      Else
         stack.Push(table.Name)
         For Each child As DbsChildTable In table.DbsChildTables
            Dim childTable As DbsTable = Me.mDbsStructure.DbsTables.GetTable(child.Name)
            If childTable Is Nothing Then
               Utility.LogForUser("Corruption - child table not found", LogId.NotFoundTable, table, child)
            Else
               htable.DbsHTables.Add(GetHierarchyNode(stack, childTable))
            End If
         Next
         stack.Pop()
      End If
      Return htable
   End Function

   Private Function IsInConstraint( _
             ByVal constraintRows As DataRow(), _
             ByVal columnName As String) _
             As Boolean
      For Each row As DataRow In constraintRows
         If String.Compare(row("column_name").ToString, columnName) = 0 Then
            Return True
         End If
      Next
      Return False
   End Function

   Private Sub AddTablePrivileges( _
            ByVal tableRow As DataRow, _
            ByVal dbsTable As DbsTable)
      Dim privilegeRows As DataRow() = Me.mTablePrivilegesTable.Select(TableMatch(tableRow))
      For Each privilegeRow As Data.DataRow In privilegeRows
         Dim privilege As New DbsTablePrivilege(mDbsStructures, _
                  privilegeRow("GRANTOR").ToString, _
                  privilegeRow("GRANTEE").ToString, _
                  privilegeRow("PRIVILEGE_TYPE").ToString)
         dbsTable.DbsTablePrivileges.Add(privilege)
      Next
   End Sub

   Private Sub AddViewPrivileges( _
            ByVal tableRow As DataRow, _
            ByVal dbsView As DbsView)
      Dim privilegeRows As DataRow() = Me.mTablePrivilegesTable.Select(TableMatch(tableRow))
      For Each privilegeRow As Data.DataRow In privilegeRows
         Dim privilege As New DbsViewPrivilege(mDbsStructures, _
                  privilegeRow("GRANTOR").ToString, _
                  privilegeRow("GRANTEE").ToString, _
                  privilegeRow("PRIVILEGE_TYPE").ToString)
         dbsView.DbsViewPrivileges.Add(privilege)
      Next
   End Sub

   Private Delegate Sub AddTableCheckConstraintDelegate(ByVal constraint As DbsTableCheckConstraint)
   Private Sub AddTableCheckConstraints( _
            ByVal tableRow As DataRow, _
            ByVal addTableCheckConstraint As AddTableCheckConstraintDelegate)
      Dim constraintRows As DataRow() = Me.mTableCheckConstraintTable.Select(TableMatch(tableRow))
      For Each constraintRow As DataRow In constraintRows
         Dim constraint As New DbsTableCheckConstraint(mDbsStructures, _
                  tableRow("TABLE_NAME").ToString, constraintRow("CHECK_CLAUSE").ToString)
         addTableCheckConstraint(constraint)
      Next
   End Sub

   Private Sub AddUdtCheckConstraints( _
            ByVal udtRow As DataRow, _
            ByVal dbsUdt As DbsUserDefinedType)
      Dim constraintRows As DataRow() = Me.mUdtCheckConstraintTable.Select(DomainMatch(udtRow))
      For Each constraintRow As DataRow In constraintRows
         Dim uniqueName As String = constraintRow("DOMAIN_NAME").ToString
         Dim clause As String = constraintRow("CHECK_CLAUSE").ToString
         Dim constraint As New DbsCheckConstraint(mDbsStructures, uniqueName, clause)
      Next
   End Sub

   Private Sub AddTableColumnPrivileges( _
            ByVal schema As String, _
            ByVal tableName As String, _
            ByVal columnRow As DataRow, _
            ByVal dbsTableColumn As DbsTableColumn)
      Dim privilegeRows As DataRow() = Me.mColumnPrivilegesTable.Select(TableColumnMatch(schema, tableName, columnRow))
      For Each privilegeRow As Data.DataRow In privilegeRows
         Dim privilege As New DbsTablePrivilege(mDbsStructures, _
                  privilegeRow("GRANTOR").ToString, _
                  privilegeRow("GRANTEE").ToString, _
                  privilegeRow("PRIVILEGE_TYPE").ToString)
         dbsTableColumn.DbsTablePrivileges.Add(privilege)
      Next
   End Sub

   Private Sub AddViewColumnPrivileges( _
            ByVal schema As String, _
            ByVal tableName As String, _
            ByVal columnRow As DataRow, _
            ByVal dbsViewColumn As DbsViewColumn)
      Dim privilegeRows As DataRow() = Me.mColumnPrivilegesTable.Select(TableColumnMatch(schema, tableName, columnRow))
      For Each privilegeRow As Data.DataRow In privilegeRows
         Dim privilege As New DbsViewPrivilege(mDbsStructures, _
                  privilegeRow("GRANTOR").ToString, _
                  privilegeRow("GRANTEE").ToString, _
                  privilegeRow("PRIVILEGE_TYPE").ToString)
         dbsViewColumn.DbsViewPrivileges.Add(privilege)
      Next
   End Sub

   Private Delegate Sub AddColumnCheckConstraintDelegate(ByVal constraint As DbsCheckConstraint)
   Private Sub AddColumnCheckConstraints( _
         ByVal originalName As String, _
         ByVal schema As String, _
         ByVal tableName As String, _
         ByVal columnRow As DataRow, _
         ByVal addColumnCheckConstraintDelegate As AddColumnCheckConstraintDelegate)
      Dim constraintRows As DataRow() = Me.mColumnCheckConstraintTable.Select(TableColumnMatch(schema, tableName, columnRow))
      For Each constraintRow As DataRow In constraintRows
         Dim constraint As New DbsCheckConstraint(mDbsStructures, _
                  originalName, constraintRow("CHECK_CLAUSE").ToString)
         addColumnCheckConstraintDelegate(constraint)
      Next
   End Sub

   Private Sub AddPrimaryKeys( _
            ByVal tableRow As DataRow, _
            ByVal dbsTable As DbsTable)
      Dim primaryKeyRows As DataRow() = mPrimaryKeyTable.Select(TableMatch(tableRow))
      Select Case primaryKeyRows.Length
         Case 0
            Utility.LogForUser("Table is missing primary key", LogId.MissingPrimaryKey, dbsTable)
         Case 1
            dbsTable.DbsPrimaryKeys.Add(New DbsPrimaryKey(mDbsStructures))
            Dim fieldRows As DataRow() = mKeyColumnTable.Select(ConstraintMatch(primaryKeyRows(0)))
            For Each fieldRow As DataRow In fieldRows
               dbsTable.DbsPrimaryKeys(0).DbsPKFields.Add(New DbsPKField(mDbsStructures, fieldRow("Column_Name").ToString, CInt(fieldRow("Ordinal_Position"))))
            Next
         Case Else
            Utility.LogForUser("Table has multiple primary keys reported (not fields, but apparently indexes)", LogId.StructureCorruption, dbsTable)
      End Select
   End Sub

   Private Sub AddUniqueKeys( _
         ByVal tableRow As DataRow, _
         ByVal dbsTable As DbsTable)
      Dim uniqueRows As DataRow() = mUniqueTable.Select(TableMatch(tableRow))
      For Each uniqueRow As DataRow In uniqueRows
         Dim uniqueKey As New DbsUniqueKey(mDbsStructures)
         dbsTable.DbsUniqueKeys.Add(uniqueKey)
         Dim fieldRows As DataRow() = mKeyColumnTable.Select(ConstraintMatch(uniqueRow))
         For Each fieldRow As DataRow In fieldRows
            uniqueKey.DbsUniqueFields.Add(New DbsUniqueField(mDbsStructures, fieldRow("Column_Name").ToString, CInt(fieldRow("Ordinal_Position"))))
         Next
      Next
      Dim tableName As String = tableRow("table_name").ToString
      uniqueRows = mUniqueIndexTable.Select("TableName = '" & tableName & "'")
      For Each uniqueRow As DataRow In uniqueRows
         Dim keyId As Int32 = CInt(uniqueRow("index_id"))
         Dim uniqueKey As New DbsUniqueKey(mDbsStructures)
         dbsTable.DbsUniqueKeys.Add(uniqueKey)
         ' NOTE: This is not designed to work with multiple owners/schemas
         Dim fieldRows As DataRow() = mUniqueIndexColumnTable.Select( _
                  "TableName = '" & tableName & "' and index_id = " & keyId.ToString())
         For i As Int32 = 0 To fieldRows.Length - 1
            Dim fieldRow As DataRow = fieldRows(i)
            uniqueKey.DbsUniqueFields.Add(New DbsUniqueField(mDbsStructures, fieldRow("ColumnName").ToString, i + 1))
         Next
      Next
   End Sub

   Private Sub AddParentTables( _
            ByVal tableRow As DataRow, _
            ByVal dbsTable As DbsTable)
      Dim foreignKeyRows As DataRow() = mReferentialTable.Select("ChildTableName = '" & tableRow("table_name").ToString & "'")
      For Each foreignKeyRow As DataRow In foreignKeyRows
         Dim dbsParentTable As New DbsParentTable(mDbsStructures, foreignKeyRow("ParentTableName").ToString)
         Dim childKeyRows As DataRow() = mKeyColumnTable.Select(ConstraintMatch(foreignKeyRow("ChildConstraintCatalog").ToString, foreignKeyRow("ChildConstraintSchema").ToString, foreignKeyRow("ChildConstraintName").ToString))
         Dim parentKeyRows As DataRow() = mKeyColumnTable.Select(ConstraintMatch(foreignKeyRow("ParentConstraintCatalog").ToString, foreignKeyRow("ParentConstraintSchema").ToString, foreignKeyRow("ParentConstraintName").ToString))
         If childKeyRows.Length <> parentKeyRows.Length Then
            Utility.LogForUser("Different number of keys in parent and child of parent relation", LogId.StructureCorruption, dbsTable)
         Else
            For i As Int32 = 0 To childKeyRows.Length - 1
               Dim dbsParentKey As New DbsParentKeyField(mDbsStructures, parentKeyRows(i)("COLUMN_NAME").ToString, CInt(parentKeyRows(i)("ORDINAL_POSITION")))
               Dim dbsChildKey As New DbsChildField(mDbsStructures, childKeyRows(i)("COLUMN_NAME").ToString, CInt(childKeyRows(i)("ORDINAL_POSITION")))
               dbsParentTable.DbsParentKeyFields.Add(dbsParentKey)
               dbsParentTable.DbsChildFields.Add(dbsChildKey)
            Next
         End If
         dbsTable.DbsParentTables.Add(dbsParentTable)
      Next
   End Sub

   Private Sub AddChildTables( _
         ByVal tableRow As DataRow, _
         ByVal dbsTable As DbsTable)
      Dim foreignKeyRows As DataRow() = mReferentialTable.Select("ParentTableName = '" & tableRow("table_name").ToString & "'")
      For Each foreignKeyRow As DataRow In foreignKeyRows
         Dim dbsChildTable As New DbsChildTable(mDbsStructures, foreignKeyRow("ChildTableName").ToString)
         Dim childKeyRows As DataRow() = mKeyColumnTable.Select(ConstraintMatch(foreignKeyRow("ChildConstraintCatalog").ToString, foreignKeyRow("ChildConstraintSchema").ToString, foreignKeyRow("ChildConstraintName").ToString))
         Dim parentKeyRows As DataRow() = mKeyColumnTable.Select(ConstraintMatch(foreignKeyRow("ParentConstraintCatalog").ToString, foreignKeyRow("ParentConstraintSchema").ToString, foreignKeyRow("ParentConstraintName").ToString))
         If childKeyRows.Length <> parentKeyRows.Length Then
            Utility.LogForUser("Different number of keys in parent and child of child relation", LogId.StructureCorruption, dbsTable)
         Else
            For i As Int32 = 0 To childKeyRows.Length - 1
               Dim dbsParentKey As New DbsParentKeyField(mDbsStructures, parentKeyRows(i)("COLUMN_NAME").ToString, CInt(parentKeyRows(i)("ORDINAL_POSITION")))
               Dim dbsChildKey As New DbsChildKeyField(mDbsStructures, childKeyRows(i)("COLUMN_NAME").ToString, CInt(childKeyRows(i)("ORDINAL_POSITION")))
               dbsChildTable.DbsParentKeyFields.Add(dbsParentKey)
               dbsChildTable.DbsChildKeyFields.Add(dbsChildKey)
            Next
         End If
         dbsTable.DbsChildTables.Add(dbsChildTable)
      Next
   End Sub

   Private Sub AppendTableInsertPriority()

      mTableTable.Columns.Add("LOAD_PRIORITY")

      For Each tableRow As DataRow In mTableTable.Rows
         If IsRoot(tableRow("TABLE_NAME").ToString) Then
            Dim stack As New Stack(Of String)
            assignInsertPriority(stack, tableRow, 0)
         End If
      Next
   End Sub

   Private Sub assignInsertPriority( _
                     ByVal stack As Stack(Of String), _
                     ByVal tablerow As DataRow, _
                     ByVal priority As System.Int32)
      Dim tableName As String = tablerow("TABLE_NAME").ToString
      If stack.Contains(tableName) Then
         'Console.WriteLine("Oops, looks like recursion")
         Return
      End If
      stack.Push(tableName)
      If ((tablerow.IsNull("LOAD_PRIORITY")) OrElse (priority > Convert.ToInt32(tablerow("LOAD_PRIORITY")))) Then
         tablerow("LOAD_PRIORITY") = priority
      End If
      Dim childRows As DataRow() = mReferentialTable.Select("ParentTableName = '" & tableName & "'")
      For Each childRow As DataRow In childRows
         Dim childTableRows As DataRow() = tablerow.Table.Select("TABLE_NAME = '" & childRow("ChildTableName").ToString & "'")
         assignInsertPriority(stack, childTableRows(0), priority + 1)
      Next
      stack.Pop()
   End Sub

   Private Function IsRoot( _
                     ByVal TableName As String) _
                     As Boolean
      Dim childRows As DataRow() = mReferentialTable.Select("ChildTableName = '" & TableName & "' AND ParentTableName <> '" & TableName & "'")
      Return (childRows.Length = 0)
   End Function

   Private Function IsAutoIncrement( _
            ByVal columnInfoTable As DataTable, _
            ByVal columnName As String, _
            ByVal dbsTableOrView As WrapperBase) _
            As Boolean
      Dim columnRows As DataRow() = columnInfoTable.Select("Column_Name = '" & columnName & "'")
      Select Case columnRows.Length
         Case 0
            Utility.LogForUser("Column not found", LogId.NotFoundColumn, dbsTableOrView)
         Case 1
            Dim typeName As String = columnRows(0)("TYPE_NAME").ToString
            Return (typeName.IndexOf("identity") > 0)
         Case Else
            Utility.LogForUser("Multiple columns have the same name", LogId.StructureCorruption, dbsTableOrView)
      End Select
      Return False
   End Function
#End Region


#Region "Data access"

   Private Function TableMatch( _
                ByVal tableRow As Data.DataRow) _
                As String
      Return "table_catalog = '" & tableRow("table_catalog").ToString & _
               "' and table_schema = '" & tableRow("table_schema").ToString & _
               "' and table_name = '" & tableRow("table_name").ToString & "'"
   End Function

   Private Function TableMatch( _
               ByVal schema As String, _
               ByVal tableName As String) _
               As String
      Return "table_schema = '" & schema & _
               "' and table_name = '" & tableName & "'"
   End Function

   Private Function TableColumnMatch( _
                ByVal tableRow As DataRow, _
                ByVal columnRow As DataRow) _
                As String
      Return TableColumnMatch(tableRow("table_Schema").ToString, tableRow("table_Name").ToString, columnRow)
   End Function

   Private Function TableColumnMatch( _
              ByVal schema As String, _
              ByVal tableName As String, _
              ByVal columnRow As DataRow) _
              As String
      Return TableMatch(schema, tableName) & " and column_name='" & _
                        columnRow("column_name").ToString & "'"
   End Function

   Private Function ConstraintMatch(ByVal constraintRow As DataRow) As String
      Return ConstraintMatch(constraintRow("constraint_catalog").ToString, _
               constraintRow("constraint_schema").ToString, _
               constraintRow("constraint_name").ToString)
   End Function

   Private Function ConstraintMatch( _
            ByVal catalogName As String, _
            ByVal schemaName As String, _
            ByVal name As String) _
            As String
      Return "constraint_catalog = '" & catalogName & _
               "' and constraint_schema = '" & schemaName & _
               "' and constraint_name = '" & name & "'"
   End Function

   Private Function ProcedureMatch( _
             ByVal procedureRow As Data.DataRow) _
             As String
      Return "specific_catalog = '" & procedureRow("specific_catalog").ToString & _
               "' and specific_schema = '" & procedureRow("specific_schema").ToString & _
               "' and specific_name = '" & procedureRow("specific_name").ToString & "'"
   End Function

   Private Function DomainMatch(ByVal domainRow As DataRow) As String
      Return "domain_catalog = '" & domainRow("domain_catalog").ToString & _
               "' and domain_schema = '" & domainRow("domain_schema").ToString & _
               "' and domain_name = '" & domainRow("domain_name").ToString & "'"
   End Function

   Private Function GetPrimaryKeyTable() As DataTable
      Return DataTableFromSql("SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE (constraint_type = 'PRIMARY KEY')")
   End Function

   Private Function GetUniqueTable() As DataTable
      Return DataTableFromSql("SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE (constraint_type = 'UNIQUE')")
   End Function

   Private Function GetUniqueIndexTable() As DataTable
      Return DataTableFromSql("select sys.indexes.name, sys.objects.name as TableName, sys.indexes.index_id, sys.indexes.is_unique, sys.indexes.is_primary_key" & _
               " from sys.indexes inner join sys.objects on sys.indexes.object_id = sys.objects.object_id " & _
               " where is_unique != 0 and is_primary_key = 0 order by sys.indexes.name")
   End Function

   Private Function GetUniqueIndexColumnTable() As DataTable
      Return DataTableFromSql("select sys.indexes.name as IndexName, sys.objects.name as TableName, sys.columns.name as ColumnName, sys.indexes.index_id  " & _
                  " from sys.indexes inner join sys.objects on sys.indexes.object_id = sys.objects.object_id inner join sys.index_columns on sys.indexes.object_id = sys.index_columns.object_id and sys.Indexes.index_id = sys.index_columns.index_id  inner join sys.columns on sys.indexes.object_id = sys.columns.object_id and sys.index_columns.column_id = sys.columns.column_id " & _
                  " where is_unique != 0 and is_primary_key = 0  and sys.indexes.name != 'cl' and sys.indexes.name != 'clst' and " & _
                  "			sys.indexes.name != 'clust' and sys.indexes.name != 'nc' and sys.indexes.name != 'nc1' and " & _
                  "			sys.indexes.name != 'nc2' and sys.indexes.name != 'nc3' and sys.indexes.name != 'queue_clustered_index' and " & _
                  "			sys.indexes.name !='queue_secondary_index'   " & _
                  "order by sys.indexes.name ")

   End Function

   Private Function GetKeyColumnTable() As DataTable
      Return DataTableFromSql("SELECT  * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE")
   End Function

   Private Function GetTablePrivilegesTable() As DataTable
      Return DataTableFromSql("SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES")
   End Function

   Private Function GetColumnPrivilegesTable() As DataTable
      Return DataTableFromSql("SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES")
   End Function

   Private Function GetColumnCheckConstraintsTable() As DataTable
      Return DataTableFromSql("SELECT Table_Catalog, Table_Schema, Table_Name, Column_Name, Check_Clause " & _
               " FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As Usage" & _
               " INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS As Constraints " & _
               " ON Usage.Constraint_Catalog = Constraints.Constraint_Catalog AND " & _
               "  Usage.Constraint_Schema = Constraints.Constraint_Schema AND " & _
               "  Usage.Constraint_Name = Constraints.Constraint_Name")
   End Function

   Private Function GetTableCheckConstraintsTable() As DataTable
      Return DataTableFromSql("SELECT Table_Catalog, Table_Schema, Table_Name, Check_Clause " & _
               " FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE As Usage" & _
               " INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS As Constraints " & _
               "    ON Usage.Constraint_Catalog = Constraints.Constraint_Catalog AND " & _
               "       Usage.Constraint_Schema = Constraints.Constraint_Schema AND  " & _
               "       Usage.Constraint_Name = Constraints.Constraint_Name")
   End Function

   Private Function GetUdtCheckConstraintsTable() As DataTable
      Return DataTableFromSql("SELECT Domain_Catalog, Domain_Schema, Domain_Name, Check_Clause " & _
               " FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS As Usage" & _
               " INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS As Constraints" & _
               "  ON Usage.Constraint_Catalog = Constraints.Constraint_Catalog AND " & _
               "     Usage.Constraint_Schema = Constraints.Constraint_Schema AND " & _
               "     Usage.Constraint_Name = Constraints.Constraint_Name")
   End Function

   Private Function GetReferentialTable() As DataTable
      Return DataTableFromSql("SELECT Parent.TABLE_NAME AS ParentTableName, " & _
                  "	Child.TABLE_NAME AS ChildTableName, " & _
                  "	Child.CONSTRAINT_CATALOG AS ChildConstraintCatalog, " & _
                  "	Child.CONSTRAINT_SCHEMA AS ChildConstraintSchema, " & _
                  "	Child.CONSTRAINT_NAME AS ChildConstraintName, " & _
                  "	Parent.CONSTRAINT_CATALOG AS ParentConstraintCatalog, " & _
                  "	Parent.CONSTRAINT_SCHEMA AS ParentConstraintSchema, " & _
                  "	Parent.CONSTRAINT_NAME AS ParentConstraintName " & _
                  "FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS Parent  " & _
                  "INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As Constraints " & _
                  "ON Constraints.UNIQUE_CONSTRAINT_CATALOG = Parent.CONSTRAINT_CATALOG AND " & _
                  "		Constraints.UNIQUE_CONSTRAINT_SCHEMA = Parent.CONSTRAINT_SCHEMA AND " & _
                  "      Constraints.UNIQUE_CONSTRAINT_NAME = Parent.CONSTRAINT_NAME " & _
                  "INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS Child " & _
                  "ON Constraints.CONSTRAINT_CATALOG = Child.CONSTRAINT_CATALOG AND " & _
                  "		Constraints.CONSTRAINT_SCHEMA = Child.CONSTRAINT_SCHEMA AND " & _
                  "		Constraints.CONSTRAINT_NAME = Child.CONSTRAINT_NAME ")
   End Function

   Private Function GetColumnInfoTable(ByVal tableName As String) As DataTable
      ' I can't get this function to work with parameters, and I'm not trying that hard
      ' because this is a private funciton and the tableName comes from the database, making
      ' SQL injection not likely.
      Return DataTableFromSql("sp_Columns '" & tableName & "'")
   End Function

   ''' <summary>
   ''' 
   ''' </summary>
   ''' <returns></returns>
   ''' <remarks>
   ''' The GetSchema information is only available for SQL Server2005. 
   ''' I'm going with the least common denominator for the moment, but this 
   ''' means we're missing some 2005 specific information. 
   ''' </remarks>
   Private Function GetUdtTable() As DataTable
      Return DataTableFromSql("SELECT * FROM INFORMATION_SCHEMA.DOMAINS")
   End Function

   Private Function GetConstraintRows( _
            ByVal constraintTable As DataTable, _
            ByVal tableName As String, _
            ByVal schemaName As String, _
            ByVal dbsTableOrView As WrapperBase) _
            As DataRow()
      Dim keyRows As DataRow() = constraintTable.Select( _
                     "table_schema = '" & schemaName & _
                     "' and table_name = '" & tableName & "'")
      Dim primaryKeys As New List(Of DataRow)
      If keyRows.Length > 1 And constraintTable Is mPrimaryKeyTable Then
         Utility.LogForUser("Table has multiple primary keys reported (not fields, but apparently indexes)", LogId.StructureCorruption, dbsTableOrView)
      ElseIf keyRows.Length = 0 And constraintTable Is mPrimaryKeyTable Then
         Utility.LogForUser("Table is missing primary key", LogId.MissingPrimaryKey, dbsTableOrView)
      Else
         For Each keyRow As DataRow In keyRows
            Dim constraintSchema As String = keyRow("constraint_schema").ToString
            Dim constraintName As String = keyRow("constraint_name").ToString
            ' The restriction on schema was removed becuase AdventureWorksLT somehow does not have the schema
            Dim tempPrimaryKeys() As DataRow = Me.mIndexColumnsTable.Select( _
                    "constraint_name = '" & constraintName & "'", _
                    "ordinal_position ASC")
            'Dim tempPrimaryKeys() As DataRow = Me.mIndexColumnsTable.Select( _
            '         "constraint_schema = '" & constraintSchema & _
            '         "' and constraint_name = '" & constraintName & "'", _
            '         "ordinal_position ASC")
            If tempPrimaryKeys.Count = 0 Then
               ' TODO: Add logging, this is bad
               Console.WriteLine("No primary key")
            End If
            For Each row As DataRow In tempPrimaryKeys
               primaryKeys.Add(row)
            Next
         Next
      End If
      Return primaryKeys.ToArray()
   End Function

   Private Function DataTableFromSql( _
            ByVal commandText As String, _
            ByVal ParamArray params() As Object) As DataTable
      Dim datatable As New DataTable()
      Dim dataAdapter As New SqlDataAdapter( _
               commandText, _
               mConnection)
      For i As Int32 = 0 To params.Length - 1 Step 2
         dataAdapter.SelectCommand.Parameters.AddWithValue(params(i).ToString, params(i + 1))
      Next
      dataAdapter.Fill(datatable)
      Return datatable
   End Function

   Private Function RunStoredProc( _
            ByVal spName As String, _
            ByVal dbsParameters As DbsStoredProcParameterCollection) _
            As DataSet
      Dim ds As New Data.DataSet
      Dim da As New SqlClient.SqlDataAdapter
      Dim cmd As New SqlClient.SqlCommand
      Dim def As Object
      Dim tr As SqlClient.SqlTransaction = Nothing

      cmd.Connection = mConnection
      cmd.CommandType = CommandType.StoredProcedure
      cmd.CommandText = spName
      For Each param As DbsStoredProcParameter In dbsParameters
         Select Case param.Type.ToLower
            Case "char", "varchar", "nchar", "nvarchar"
               def = String.Empty
            Case "uniqueidentifier"
               def = DBNull.Value
            Case "datetime"
               def = "1/1/1873"
            Case Else
               def = 0
         End Select
         cmd.Parameters.Add(New SqlClient.SqlParameter(param.Name, def))
      Next
      da.SelectCommand = cmd
      Try
         cmd.Connection.Open()
         cmd.CommandTimeout = 5
         tr = cmd.Connection.BeginTransaction(IsolationLevel.Serializable)
         cmd.Transaction = tr
         da.Fill(ds)
         'Catch ex As Exception
         '   Utility.LogForUser("Error running stored proc " & spName, LogId.StoredProcedureCallFailed)
      Finally
         If tr IsNot Nothing Then
            tr.Rollback()
         End If
         cmd.Connection.Close()
      End Try
      Return ds

   End Function

#End Region


End Class
